package cn.cloud.butler.scheduled.repository;

import cn.cloud.butler.scheduled.domain.OperStateEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import javax.transaction.Transactional;
import java.util.List;

public interface OperStateRepository extends JpaRepository<OperStateEntity,Long> {

    @Query(value=" select distinct t.MacSN from OperState t where date(t.UploadTime)= ?1 ", nativeQuery = true)
    List<String> selectMac(String preDate);

    @Query(value=" select * from OperState where MacSN = ?1 AND date(UploadTime) = ?2  order by UploadTime ASC", nativeQuery = true)
    List<OperStateEntity> SelOneDayState(String macSn,String predate);

    @Query(value=" SELECT * FROM OperState where UploadTime=(select max(UploadTime) from OperState where MacSN=?1 AND DATE(UploadTime) < ?2) AND MacSN=?1 limit 1", nativeQuery = true)
    OperStateEntity selLastState(String macSn,String preDate);

    @Modifying
    @Transactional
    @Query(value=" delete from OperState WHERE TO_DAYS(NOW( ) ) - TO_DAYS( UploadTime) = 3", nativeQuery = true)
    void delStateByThreeDayAgo();

}
